﻿/*
 * Google Spreadsheets Helper Class by Openigloo.org
 * Copyright (C) 2011 Openigloo.org
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
 
 
using System;
using System.Collections.Generic;
using Google.GData.Client;
using Google.GData.Extensions;
using Google.GData.Spreadsheets;

namespace igloo_GoogleSpreadsheetsHelper
{
/// <summary>
	/// a helper class for connecting to google's spreadsheets API.
	/// Developed for use in the igl00 project by developers wishing to connect 
	/// .Net apps to data stored in google spreadsheets.
	/// </summary>
	public class GoogSpreadsheet
	{
		//PRIVATE VARIABLES
		private string gUserName, gPassword;
		private string gApplicationName = "igl00_appRequest_031911";
		
		//PUBLIC VARIABLES
		public string gSpreadsheetTitle;
		public int gWorksheetNumber;
		public int numParameters;
		public int numRows;
		
		public string[ , ] gSpreadsheetData;
		
		//CONSTRUCTOR
		public GoogSpreadsheet(string username, string password, string title, int worksheet, int parameters)
		{
			gUserName = username;
			gPassword = password;
			gSpreadsheetTitle = title;
			gWorksheetNumber = worksheet;
			numParameters = parameters;
		}
		
		//METHODS
		public void GetData()
		{
			//start up the spreadsheets api service with gDocs
			SpreadsheetsService myService = new SpreadsheetsService(gApplicationName);
			
			//authenticate
			myService.setUserCredentials(gUserName, gPassword);
			
			//pull in the spreadsheet matching the title query
			SpreadsheetQuery squery = new SpreadsheetQuery();
			squery.Title = gSpreadsheetTitle;
			SpreadsheetFeed sfeed = myService.Query(squery);
			
			//create a worksheet feed - a feed of all the worksheets (tabs) in a spreadsheet 
			//the 0 in the feed.Entries array grabs the first spreadsheet in the list of spreadsheets.
			//the feed contains all of the worksheets in the doc.
			AtomLink link = sfeed.Entries[0].Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
			WorksheetQuery wquery = new WorksheetQuery(link.HRef.ToString());
			WorksheetFeed wfeed = myService.Query(wquery);
			
			//create a list based feed - a list corresponds to a row of a worksheet
			//essentially, a list of rows.
			//just like above, the 0 in the array grabs the first worksheet in the list of wroksheets
			AtomLink listFeedLink = wfeed.Entries[gWorksheetNumber].Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
			ListQuery lquery = new ListQuery(listFeedLink.HRef.ToString());
			ListFeed lfeed = myService.Query(lquery);
			
			//make the number of rows in a table accessable
			numRows = lfeed.Entries.Count;
			
			//bh code
			//dimension then populate 2d array of strings - a 1:1 mapping of the worksheet in an array
			
			gSpreadsheetData = new string[lfeed.Entries.Count, numParameters+1];

			int i=0;
			int j=0;
			foreach (ListEntry worksheetRow in lfeed.Entries)
			{
			    ListEntry.CustomElementCollection elements = worksheetRow.Elements;
			    foreach (ListEntry.Custom element in elements) 
			    {
			    	gSpreadsheetData[i,j] = element.Value;
			    	j++;
			    	if ( j==numParameters+1 ) {
			    		j=0;
			    	}
			    }
			    i++;
			}
		}
	}
}